package com.decent.ejfadmin.monitor.dao;

import cn.decent.component.shiro.entity.Admin;
import com.decent.ejfadmin.common.bean.OrderMonitor;
import com.decent.ejfadmin.monitor.response.SupplyCardFace;
import com.decent.ejfadmin.monitor.response.SupplyCardSpeed;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 监控
 *
 * @author weicx
 * @date 2018/08/21
 */
@Mapper
@Repository
public interface MonitorDao {

    /**
     * 底部监控
     *
     * @param startTime
     * @return
     */
    @Select("SELECT\n" +
            "\tsum(t.preJudge) preJudge,\n" +
            "\tsum(t.wrongFace) wrongFace,\n" +
            "\tsum(t.waitSlowTrySell) waitSlowTrySell\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tCASE sc.`status`\n" +
            "\t\tWHEN 4 THEN\n" +
            "\t\t\tcount(1)\n" +
            "\t\tELSE\n" +
            "\t\t\t0\n" +
            "\t\tEND preJudge,\n" +
            "\t\tCASE sc.`status`\n" +
            "\tWHEN 5 THEN\n" +
            "\t\tcount(1)\n" +
            "\tELSE\n" +
            "\t\t0\n" +
            "\tEND wrongFace,\n" +
            "\tCASE sc.`status`\n" +
            "WHEN 7 THEN\n" +
            "\tcount(1)\n" +
            "ELSE\n" +
            "\t0\n" +
            "END waitSlowTrySell\n" +
            "FROM\n" +
            "\tyuhuang_supply_card sc\n" +
            "WHERE\n" +
            "\tsc.startTime >= #{startTime}\n" +
            "\tAND sc.`status` IN (4, 5, 7)\n" +
            "GROUP BY\n" +
            "\tsc.`status`\n" +
            "\t) t")
    OrderMonitor queryTotal(@Param("startTime") String startTime);

    /**
     * 查询转账异常订单数量
     *
     * @param startTime 开始时间
     * @return 返回数量
     */
    @Select(" select COUNT(1) from yuhuang_withdraw_record where state = 4 and startTime >= #{startTime} ")
    int queryTransferAbnormalCount(@Param("startTime") String startTime);

    /**
     * 查询该职务下是否有管理员
     *
     * @return
     */
    @Select("SELECT id ,`name` from tbl_shiro_admin ")
    List<Admin> hasAdmin();

    /**
     * 销卡速度监控
     *
     * @return
     */
    @Select("SELECT\n" +
            "\tt2.area,\n" +
            "\tt2.productType,\n" +
            "\tt2.face,\n" +
            "\tsum(t2.oneMinute) oneMinute\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tsc2.productType,\n" +
            "\t\t\tsc2.face,\n" +
            "\t\t\tsc2.endTime,\n" +
            "\n" +
            "\t\tIF (\n" +
            "\t\t\tsc2.supplyArea = 0,\n" +
            "\t\t\t'全国',\n" +
            "\t\t\tsc2.area\n" +
            "\t\t) area,\n" +
            "\t\tcount(1) oneMinute\n" +
            "\tFROM\n" +
            "\t\tyuhuang_supply_card sc2\n" +
            "\tWHERE\n" +
            "\t\tsc2.endTime >= date_sub(now(), INTERVAL 1 MINUTE)\n" +
            "\tAND sc2. STATUS = 1\n" +
            "\tGROUP BY\n" +
            "\t\tsc2.productType,\n" +
            "\t\tsc2.face,\n" +
            "\t\tsc2.supplyArea,\n" +
            "\t\tsc2.area,\n" +
            "\t\tsc2.endTime\n" +
            "\t) t2\n" +
            "GROUP BY\n" +
            "\tt2.area,\n" +
            "\tt2.productType,\n" +
            "\tt2.face")
    List<SupplyCardSpeed> getSupplyCardSpeed();

    /**
     * 当日销卡面值监控
     *
     * @param startTime 时间
     * @return
     */
    @Select("select b.*,o.card_operator_name operatorName from yuhuang_card_operator o,(SELECT\n" +
            "\tmain.area,\n" +
            "\tmain.face,\n" +
            "\tmain.productType,\n" +
            "\tsum(main.successCount) successCount,\n" +
            "\tsum(main.failCount) failCount,\n" +
            "\tsum(main.total) total\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tarea,\n" +
            "\t\t\tproductType,\n" +
            "\t\t\tCASE STATUS\n" +
            "\t\tWHEN 2 THEN\n" +
            "\t\t\tcount(1)\n" +
            "\t\tELSE\n" +
            "\t\t\t0\n" +
            "\t\tEND failCount,\n" +
            "\t\tCASE STATUS\n" +
            "\tWHEN 1 THEN\n" +
            "\t\tcount(1)\n" +
            "\tELSE\n" +
            "\t\t0\n" +
            "\tEND successCount,\n" +
            "\tcount(1) total,\n" +
            "\tface\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\n" +
            "\t\tIF (supplyArea = 0, '全国', area) area,\n" +
            "\t\tSTATUS,\n" +
            "\t\tproductType,\n" +
            "\t\tface\n" +
            "\tFROM\n" +
            "\t\tyuhuang_supply_card sc\n" +
            "\tWHERE\n" +
            "\t\tsc.startTime >= #{startTime}\n" +
            "\t) tes\n" +
            "GROUP BY\n" +
            "\tarea,\n" +
            "\tSTATUS,\n" +
            "\tproductType,\n" +
            "\tface\n" +
            "\t) main\n" +
            "GROUP BY\n" +
            "\tmain.productType,\n" +
            "\tface,\n" +
            "\tarea) b where o.card_operator_id=b.productType ")
    List<SupplyCardFace> getSupplyCardFace(@Param("startTime") String startTime);

    /**
     * 提卡用户数（成功销卡)
     *
     * @param startTime
     * @return
     */
    @Select("SELECT" +
            " COUNT(1)" +
            "FROM" +
            " (" +
            "  SELECT" +
            "   userId" +
            "  FROM " +
            "   yuhuang_supply_card " +
            "  WHERE " +
            "   startTime >= #{startTime} " +
            "  AND `status` = 1 " +
            "  GROUP BY " +
            "   userId " +
            " ) t")
    int getSuccessTotal(@Param("startTime") String startTime);

    /**
     * 当日新增提卡用户数（之前从未提过卡的）
     *
     * @param startTime
     * @return
     */
    @Select("SELECT\n" +
            "  sc.userId\n" +
            "FROM\n" +
            "  yuhuang_supply_card sc\n" +
            "LEFT JOIN yuhuang_supply_daily_sheet sh ON sc.userId = sh.userId\n" +
            "WHERE\n" +
            "  sc.startTime >= #{startTime}\n" +
            "AND sh.userId IS NULL\n" +
            "GROUP BY\n" +
            "  sc.userId")
    List<String> getNewSupplyCardUserTotal(@Param("startTime") String startTime);
}
